clear all
set more off , permanently
capture log close
set maxvar 20000

********************************************************************************
**** This file loads radio station location data and reshapes it ***************
********************************************************************************

****************************************************
** Create data set with polygons that are treated **
****************************************************		

// Load data
cd "$raw\08_Germany_radio"
import delimited "20180302_DE_stationlist.csv" , clear 


// Drop DAB only channels
drop if station_type == "DAB"

* id city region name are all constant across transmitters.

* Transmitter ID						// Not necessary.
desc *id 		// Also capture other ids such as *sid, *eid, *subchid. Drop these IDs, since we are only interested in the one transmitter ID.
drop *sid *eid *subchid
desc *id

* Transmitter band						
desc *band

* Transmitter contour
desc *contour

* Transmitter place						// Not necessary.
desc *place

* Transmitter region
desc *region
desc *regio

* Transmitter country					// Not necessary.
desc *country
desc *countr
desc *count

* Transmitter latitude
desc *latitude

* Transmitter longitude
desc *longitude


// Keep only relevant variables
keep parent_id id city region name *band *contour *region *regio *latitude *longitude

// String longitude and latitude for reshape
tostring *longitude *latitude, replace force

// Only Deutschlandfunk and Deutschlandfunk Kultur have >158 transmitters. Ignore these two stations and hence kick out transmitters above this.
tab transmitters158long
tab name if transmitters158long != "."

foreach X of numlist 158(1)334{
drop transmitters`X'*
}

// Reshape data
reshape long transmitters , i(parent_id id city region name) j(pepe) string

// Split pepe
split pepe , p("cont" , "la", "lon", "trans" , "ba")

// Drop observations which do not include any information in transmitters
drop if transmitters == "" | transmitters == "."

// Clean pepe2
replace pepe2 = "longitude" if pepe2 == "gitude"
replace pepe2 = "latitude" if pepe2 == "titude"
replace pepe2 = "contour" if pepe2 == "our"
replace pepe2 = "region" if pepe2 == "mitter_region" | pepe2 == "mitter_regio"
replace pepe2 = "band" if pepe2 == "nd"

// Rename variable
rename transmitter transmitter_
rename pepe1 transmitter_nr
drop pepe

// Reshape wide
reshape wide transmitter_ , i(parent_id id city region name transmitter_nr) j(pepe2) string

// Clean data
destring transmitter_nr , replace
destring transmitter_longitude transmitter_latitude , replace
sort parent_id id transmitter_nr

// Save data set
cd "$dta"
compress
save radio_transmitters.dta , replace

